#ifndef CUSTOMERDATA_H
#define CUSTOMERDATA_H

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QDebug>
#include <QVariant>

class CustomerData {
public:
    CustomerData() {
        // 初始化数据库连接
        initializeDatabase();
    }

    ~CustomerData() {
        db.close();  // 关闭数据库连接
    }


    bool addCustomer(const QString& name, const QString& email) {
        QSqlQuery checkQuery(db);
        checkQuery.prepare("SELECT COUNT(*) FROM customers WHERE email = :email");
        checkQuery.bindValue(":email", email);
        if (!checkQuery.exec()) {
            qDebug() << "检查客户失败：" << checkQuery.lastError().text();
                                                                        return false;
        }
        if (checkQuery.next() && checkQuery.value(0).toInt() > 0) {
            qDebug() << "客户已存在，不能重复添加";
            return false;
        }

        QSqlQuery insertQuery(db);
        insertQuery.prepare("INSERT INTO customers (name, email) VALUES (:name, :email)");
        insertQuery.bindValue(":name", name);
        insertQuery.bindValue(":email", email);
        if (!insertQuery.exec()) {
            qDebug() << "添加客户失败：" << insertQuery.lastError().text();
            return false;
        }
        return true;
    }

    // 从数据库中删除客户
    bool removeCustomer(int id) {
        QSqlQuery query(db);
        query.prepare("DELETE FROM customers WHERE id = :id");
        query.bindValue(":id", id);
        if (!query.exec()) {
            qDebug() << "删除客户失败：" << query.lastError().text();
            return false;
        }
        if (query.numRowsAffected() == 0) {
            qDebug() << "没有找到需要删除的客户，ID可能不存在";
            return false;
        }
        return true;
    }


    // 更新客户信息

    bool updateCustomer(int id, const QString& name, const QString& email) {
        QSqlQuery query(db);
        query.prepare("UPDATE customers SET name = :name, email = :email WHERE id = :id");
        query.bindValue(":name", name);
        query.bindValue(":email", email);
        query.bindValue(":id", id);
        if (!query.exec()) {
             qDebug() << "更新客户信息失败：" << query.lastError().text();
                                                                                 return false;
        }
        if (query.numRowsAffected() == 0) {
            qDebug() << "未找到需要更新的客户，ID可能不存在";
            return false;
        }
        return true;
    }

    QList<QPair<int, QString>> getAllCustomers() {
        QList<QPair<int, QString>> customers;
        QSqlQuery query("SELECT id, name FROM customers", db);
        while (query.next()) {
            int id = query.value(0).toInt();
            QString name = query.value(1).toString();
            customers.append(qMakePair(id, name));
        }
        return customers;
    }

private:
    QSqlDatabase db;

    // 初始化数据库连接
    void initializeDatabase() {
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("customer_db.sqlite");

        if (!db.open()) {
            qDebug() << "数据库连接失败：" << db.lastError().text();
            return;
        }

        // 检查表是否存在，如果不存在则创建
        QSqlQuery query(db);
        if (!query.exec("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='customers'")) {
            qDebug() << "检查表存在失败：" << query.lastError().text();
            return;
        }

        if (query.next() && query.value(0).toInt() == 0) {
            // 创建表
            if (!query.exec("CREATE TABLE customers ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "name TEXT NOT NULL, "
                "email TEXT NOT NULL)")) {
                qDebug() << "创建表失败：" << query.lastError().text();
            } else {
                qDebug() << "表 'customers' 已成功创建";
                }
            } else {
                qDebug() << "表 'customers' 已存在";
        }
    }

};
#endif // CUSTOMERDATA_H